﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo.Agent;
using System.Configuration;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Collections.Specialized;

namespace BizTalkOneService
{
    public class SqlJobs
    {
        
        //static readonly string SqlServer = Convert.ToString(ConfigurationManager.ConnectionStrings["SqlJobsConnectionString"]);

        EventLog appLog = new EventLog();
        private List<Counter> sqlJobList = null;
        private int Disabled = 0;
        private int Cancelled = 0;
        private int Failed = 0;
        private int InProgress = 0;
        private int Retry = 0;
        private int Succeeded = 0;
        private int Unknown = 0;
        private int Total = 0;
        private List<SqlServer> SqlServerList = new List<SqlServer>();


        public SqlJobs()
        {
            var SqlJobServers = ConfigurationManager.GetSection("sqljobs") as NameValueCollection;
            if (SqlJobServers != null)
            {
                foreach (var NameKey in SqlJobServers.AllKeys)
                {
                    SqlServerList.Add(new SqlServer { SqlServerKey = NameKey.ToString(), SqlServerValue = SqlJobServers.GetValues(NameKey).FirstOrDefault() });
                }
            }
        }

        public void FillJobLists()
        {
            this.sqlJobList = new List<Counter>();
            foreach (var SqlServer in SqlServerList)
            {
                try
                {
                    ServerConnection conn = new ServerConnection(SqlServer.SqlServerValue);
                    Server server = new Server(conn);
                    JobCollection jobs = server.JobServer.Jobs;
                    foreach (Job job in jobs)
                    {
                        if (job.IsEnabled == true)
                        {
                            if (job.LastRunOutcome == CompletionResult.Cancelled)
                                Cancelled++;
                            if (job.LastRunOutcome == CompletionResult.Failed)
                                Failed++;
                            if (job.LastRunOutcome == CompletionResult.InProgress)
                                InProgress++;
                            if (job.LastRunOutcome == CompletionResult.Retry)
                                Retry++;
                            if (job.LastRunOutcome == CompletionResult.Succeeded)
                                Succeeded++;
                            if (job.LastRunOutcome == CompletionResult.Unknown)
                                Unknown++;
                        }
                        if (job.IsEnabled == false)
                            Disabled++;
                        Total++;
                    }
                    string strStarted = String.Format("Succeeded ({0})", this.Total);
                    string strInProgress = String.Format("InProgress ({0})", this.Total);
                    string strRetry = String.Format("Retry ({0})", this.Total);
                    string strFailed = String.Format("Failed ({0})", this.Total);
                    string strCancelled = String.Format("Cancelled ({0})", this.Total);
                    string strUnknown = String.Format("Unknown ({0})", this.Total);
                    string strDisabled = String.Format("Disabled ({0})", this.Total);
                    string strTotal = String.Format("Total ({0})", this.Total);


                    this.sqlJobList.Add(new Counter { CounterCategory = "SQLJob", ServerName = server.ToString(), CounterName = strStarted, CounterValue = Succeeded });
                    this.sqlJobList.Add(new Counter { CounterCategory = "SQLJob", ServerName = server.ToString(), CounterName = strInProgress, CounterValue = InProgress });
                    this.sqlJobList.Add(new Counter { CounterCategory = "SQLJob", ServerName = server.ToString(), CounterName = strRetry, CounterValue = Retry });
                    this.sqlJobList.Add(new Counter { CounterCategory = "SQLJob", ServerName = server.ToString(), CounterName = strFailed, CounterValue = Failed });
                    this.sqlJobList.Add(new Counter { CounterCategory = "SQLJob", ServerName = server.ToString(), CounterName = strCancelled, CounterValue = Cancelled });
                    this.sqlJobList.Add(new Counter { CounterCategory = "SQLJob", ServerName = server.ToString(), CounterName = strUnknown, CounterValue = Unknown });
                    this.sqlJobList.Add(new Counter { CounterCategory = "SQLJob", ServerName = server.ToString(), CounterName = strDisabled, CounterValue = Disabled });
                    this.sqlJobList.Add(new Counter { CounterCategory = "SQLJob", ServerName = server.ToString(), CounterName = strTotal, CounterValue = Total });
                    Disabled = 0;
                    Cancelled = 0;
                    Failed = 0;
                    InProgress = 0;
                    Retry = 0;
                    Succeeded = 0;
                    Unknown = 0;
                    Total = 0;
                }

                catch (Exception ex)
                {
                    appLog.Source = Settings.EventLogSource;
                    EventLogEntryType eventType = EventLogEntryType.Error;
                    appLog.WriteEntry("Error retrieving Sql Jobs status: " + ex.Message.ToString() + "\n" + ex.StackTrace, eventType);
                    sqlJobList.Add(new Counter() { CounterCategory = "Error", CounterName = "SQLJob", CounterValueString = String.Format("{0}", ex.Message.ToString()) });
                }
            }
        }

        public List<Counter> GetSqlJobList()
        {
            this.FillJobLists();
            return this.sqlJobList;
        }
    }
}